package com.szt.sy.util.excel;

import java.io.File;
import java.io.FileInputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;

/***
 * 从EXCEL导入到数据库
 * 
 * @author wwy
 *
 */
public class ObjectExcelRead {
	
	/**
	 * @param filepath
	 *            //文件路径
	 * @param filename
	 *            //文件名
	 * @param startrow
	 *            //开始行号
	 * @param startcol
	 *            //开始列号
	 * @param sheetnum
	 *            //sheet
	 * @return list
	 */
	public static List<Object> readExcel(String filepath, String filename, int startrow, int startcol, int sheetnum) {
		List<Object> varList = new ArrayList<>();
		
		try {
			File target = new File(filepath, filename);
			FileInputStream fi = new FileInputStream(target);
			HSSFWorkbook wb = new HSSFWorkbook(fi);
			// sheet 从0开始
			HSSFSheet sheet = wb.getSheetAt(sheetnum);
			// 取得最后一行的行号
			int rowNum = sheet.getLastRowNum() + 1;
			// 行循环开始
			for (int i = startrow; i < rowNum; i++) {
				
				Map<String, Object> varpd = new HashMap<>(20);
				// 行
				HSSFRow row = sheet.getRow(i);
				// 每行的最后一个单元格位置
				int cellNum = row.getLastCellNum();
				// 列循环开始
				for (int j = startcol; j < cellNum; j++) {
					HSSFCell cell = row.getCell(Short.parseShort(j + ""));
					String cellValue = null;
					if (null != cell) {
						// 判断excel单元格内容的格式，并对其进行转换，以便插入数据库
						switch (cell.getCellType()) {
						case Cell.CELL_TYPE_NUMERIC:
							cellValue = new BigDecimal(cell.getNumericCellValue()).stripTrailingZeros().toPlainString()
									.trim();
							break;
						case Cell.CELL_TYPE_STRING:
							cellValue = cell.getStringCellValue().trim();
							break;
						case Cell.CELL_TYPE_FORMULA:
							cellValue = (cell.getNumericCellValue() + "").trim();
							// cellValue =
							// String.valueOf(cell.getDateCellValue());
							break;
						case Cell.CELL_TYPE_BLANK:
							cellValue = "";
							break;
						case Cell.CELL_TYPE_BOOLEAN:
							cellValue = String.valueOf(cell.getBooleanCellValue()).trim();
							break;
						case Cell.CELL_TYPE_ERROR:
							cellValue = String.valueOf(cell.getErrorCellValue()).trim();
							break;
						default:
							break;
						}
						
					} else {
						cellValue = "";
					}
					
					varpd.put("var" + j, cellValue);
					
				}
				varList.add(varpd);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println(e);
		}
		
		return varList;
	}
}
